﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xeml.Sampling.Contracts;
using System.Reflection;
using System.ComponentModel;
using System.Data.SqlClient;
using Xeml.Sampling;
using System.Data.Odbc;
using System.Data;
using MySql.Data.MySqlClient;

namespace SQLDataProviders
{
    public class MySQLDataProvider: IDataProvider
    {
        public String Username { get; set; }
        private String _password;
        public String Password { 
            set {
                _password = value;
            }
        }
        public String Server { get; set; }
        public String Database { get; set; }

        #region constructor
        /// <summary>
        /// The standard constructor
        /// </summary>
        public MySQLDataProvider()
        {
        }
        #endregion constructor

        #region Helper methods
        /// <summary>
        /// Requests an authentication for the connection.
        /// </summary>
        /// <returns></returns>
        private AuthenticationRequestEventArgs CheckAuthentication()
        {
            return new AuthenticationRequestEventArgs("mssql", "mssql");
        }

        public string ConnectionString()
        {
                MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
                sb.UserID = Username;
                sb.Password = _password;
                sb.Server = Server;
                sb.Database = Database;

                return sb.ConnectionString;
        }

        public IDbConnection BuildConnection()
        {
            return new MySqlConnection(ConnectionString());
        }

        /// <summary>
        /// Builds a sql connection from the current config and authentication method
        /// </summary>
        /// <param name="cancelled">is set to true if the user cancelled</param>
        /// <returns>The resulting sql connection.</returns>
        public IDbConnection BuildConnection(out bool cancelled)
        {
            // TODO ask for credentials
            cancelled = false;
            return BuildConnection(Username, _password);
        }

        public IDbConnection BuildConnection(string username, string pwd)
        {
            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
            sb.Server = Server;
            sb.Database = Database;
            sb.UserID = username;
            sb.Password = pwd;

            return new MySqlConnection(sb.ConnectionString);
        }

        public IDbConnection BuildConnection(string server, string database, string username, string pwd)
        {
            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
            sb.Server = server;
            sb.Database = database;
            sb.UserID = username;
            sb.Password = pwd;

            return new MySqlConnection(sb.ConnectionString);
        }
        #endregion

        #region IDataProvider
        public System.Drawing.Image Logo
        {
            get { return Properties.Resources.mysql_logo; }
        }

        public bool SupportsAutoMapping
        {
            get { return false; }
        }

        public IEnumerable<string> ListForeignKey()
        {
            yield return "Experiment";
            yield return "Chromatogram";
            yield break;
        }

        public IEnumerable<string> FilterKeyValues(string key, Dictionary<string, string> filter)
        {
            bool cancelled;

            using (MySqlCommand cmd = new MySqlCommand())
            using (cmd.Connection = (MySqlConnection)this.BuildConnection(out cancelled))
            {
                if (! cancelled)
                {
                    switch (key)
                    {
                        case "Experiment":
                            if (filter != null && filter.ContainsKey("Chromatogram") && !string.IsNullOrEmpty(filter["Chromatogram"]))
                            {
                                cmd.CommandText = @"
                                SELECT distinct e.name FROM Chromatograms c
                                JOIN ExperimentsChromatograms ec on ec.Chromatogram_id = c.id
                                join Experiments e on e.id = ec.experiment_id
	                                WHERE c.name = @chromatogram
	                                ORDER BY e.name";
                                cmd.Parameters.AddWithValue("@chromatogram", filter["Chromatogram"]);
                            }
                            else
                            {
                                cmd.CommandText = "select name from Experiments order by name";
                            }
                            break;
                        case "Chromatogram":
                            if (filter != null && filter.ContainsKey("Experiment") && !string.IsNullOrEmpty(filter["Experiment"]))
                            {
                                cmd.CommandText = @"
                                    SELECT distinct c.name FROM Chromatograms c 
                                JOIN ExperimentsChromatograms ec on ec.Chromatogram_id = c.id
                                join Experiments e on e.id = ec.experiment_id
	                                    WHERE e.name = @experiment
	                                    ORDER BY c.name";
                                cmd.Parameters.AddWithValue("@experiment", filter["Experiment"]);
                            }
                            else
                            {
                                cmd.CommandText = "select name from Chromatograms order by name";
                            }
                            break;
                        default:
                            throw new ArgumentException("key");
                    }
                    cmd.CommandType = System.Data.CommandType.Text;

                    try
                    {
                        cmd.Connection.Open();
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                yield return reader.GetString(0);
                            }
                        }
                    }
                    finally
                    {
                        cmd.Connection.Close();
                    }
                }
            }
        }

        public IEnumerable<Xeml.Document.SidMapping> AutoMap(Guid expId, string expName, IEnumerable<int> samples)
        {
            throw new NotImplementedException();
        }

        public SidLookUpResult Validate(Xeml.Document.SidMapping sim)
        {
            if (!ListForeignKey().All(x => sim.ForeignKeyMap.ContainsKey(x)))
            {
                return SidLookUpResult.UnsupportedMapping;
            }

            if (ListForeignKey().Any(x => string.IsNullOrEmpty(sim.ForeignKeyMap[x])))
            {
                return SidLookUpResult.UnsupportedMapping;
            }

            string exp = sim.ForeignKeyMap["Experiment"];
            //in real project proof whether the experiment exists in the data source 
            string sample = sim.ForeignKeyMap["Chromatogram"];

            //in real project proof whether the experiment exists in the experiment 
            return SidLookUpResult.Valid;
        }

        public Mpi.Common.Collections.KeyKeyValueCollection<string, int, object> QueryData(List<Xeml.Document.SidMapping> samples)
        {
            throw new NotImplementedException();
        }

        public event EventHandler<Xeml.Sampling.AuthenticationRequestEventArgs> AuthenticationRequest;

        public event EventHandler<Xeml.Sampling.XemlFilePathRequestEventArgs> XemlFilePathRequest;

        public string Publisher
        {
            get { return "Max Planck Institute of Molecular Plant Physiology"; }
        }

        public Version Version
        {
            get { return Assembly.GetExecutingAssembly().GetName().Version; }
        }

        public string ComponentName
        {
            get { return "MySQL data provider for the Golm Metabolome Database (GMD)"; }
        }

        public string Author
        {
            get { return "Kenny Billiau"; } 
        }

        public string Description
        {
            get { return "Data provider for a MySQL version of the GMD"; }
        }

        public string Uri
        {
            get { return "http://gmd.mpimp-golm.mpg.de/xeml/mysql/"; }
        }

        public Xeml.Document.Contracts.IXemlComponent Copy()
        {
            MySQLDataProvider mdp = new MySQLDataProvider();

            mdp.Username = this.Username;
            //mdp.Password = this._password; // this should be reentered on copy
            mdp.Server = this.Server;
            mdp.Database = this.Database;

            return mdp;
        }

        public void Configure(Xeml.SQLDataProviders.DataProviderConfiguration config)
        {
            Database = config.Database;
            Username = config.Username;
            Password = config.Password;
            Server = config.Server;
        }
        #endregion 

        public Xeml.Sampling.ConnectResult TestCredentials(string user, string password)
        {
            if (TestCredentials((MySqlConnection)BuildConnection(user, password)))
            {
                return ConnectResult.Success;
            }
            else
            {
                return ConnectResult.Failed;
            }
        }

        /// <summary>
        /// test the connectionstring
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <returns>true means 'fine', false refers to problem</returns>
        private bool TestCredentials(MySqlConnection conn)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            using (cmd.Connection = conn)
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select 1";
                cmd.Connection.Open();

                try
                {
                    return (Convert.ToInt32(cmd.ExecuteScalar()) == 1);
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }
    }
}
